import { NextRequest, NextResponse } from 'next/server';
import { SqliteDatabase } from '@/lib/sqlite-database';

// 定义完整的设备分类对应的参数表结构
const classificationTables = {
  // 配管专业
  "表 C.18": {
    tableName: "bas012_centrifugal_pump_params",
    displayName: "BAS012 离心泵基础参数表",
    fields: [
      { name: "制造厂家", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "供货厂家", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "额定转速", dataType: "数值型", measurementType: "转速", isRequired: true },
      { name: "设备名称", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "装箱单号", dataType: "字符型", measurementType: "—", isRequired: false },
      { name: "出厂日期", dataType: "日期型", measurementType: "—", isRequired: false },
      { name: "规格", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "型号", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "额定功率", dataType: "数值型", measurementType: "功率 2", isRequired: true },
      { name: "额定电流", dataType: "数值型", measurementType: "电流 1", isRequired: true }
    ]
  },
  "表 C.27": {
    tableName: "bas021_rotor_pump_params",
    displayName: "BAS021 转子泵基础参数表",
    fields: [
      { name: "制造厂家", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "供货厂家", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "设备名称", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "装箱单号", dataType: "字符型", measurementType: "—", isRequired: false },
      { name: "出厂日期", dataType: "日期型", measurementType: "—", isRequired: false },
      { name: "规格", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "型号", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "额定转速", dataType: "数值型", measurementType: "转速", isRequired: true },
      { name: "额定功率", dataType: "数值型", measurementType: "功率 2", isRequired: true },
      { name: "额定电流", dataType: "数值型", measurementType: "电流 1", isRequired: true }
    ]
  },
  "表 C.30": {
    tableName: "bas024_sliding_vane_pump_params",
    displayName: "BAS024 滑片泵基础参数表",
    fields: [
      { name: "制造厂家", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "供货厂家", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "设备名称", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "装箱单号", dataType: "字符型", measurementType: "—", isRequired: false },
      { name: "出厂日期", dataType: "日期型", measurementType: "—", isRequired: false },
      { name: "规格", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "型号", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "额定转速", dataType: "数值型", measurementType: "转速", isRequired: true },
      { name: "额定功率", dataType: "数值型", measurementType: "功率 2", isRequired: true },
      { name: "额定电流", dataType: "数值型", measurementType: "电流 1", isRequired: true }
    ]
  },
  "表 C.36": {
    tableName: "bas030_submersible_pump_params",
    displayName: "BAS030 潜液泵基础参数表",
    fields: [
      { name: "制造厂家", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "供货厂家", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "设备名称", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "装箱单号", dataType: "字符型", measurementType: "—", isRequired: false },
      { name: "出厂日期", dataType: "日期型", measurementType: "—", isRequired: false },
      { name: "规格", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "型号", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "额定功率", dataType: "数值型", measurementType: "功率 2", isRequired: true },
      { name: "扬程", dataType: "数值型", measurementType: "长度 1", isRequired: true },
      { name: "流量", dataType: "数值型", measurementType: "体积流量 1", isRequired: true }
    ]
  },
  "表 C.81": {
    tableName: "bas075_cylindrical_tank_params",
    displayName: "BAS075 圆筒形储罐基础参数表",
    fields: [
      { name: "规格", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "型号", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "制造厂家", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "供货厂家", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "设计压力", dataType: "数值型", measurementType: "压强 3", isRequired: true },
      { name: "储罐名称", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "结构形式", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "储罐公称容积", dataType: "数值型", measurementType: "容积 3", isRequired: true },
      { name: "储罐内直径", dataType: "数值型", measurementType: "长度 2", isRequired: true },
      { name: "罐壁高度", dataType: "数值型", measurementType: "长度 2", isRequired: true },
      { name: "材质规格", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "生产日期", dataType: "日期型", measurementType: "—", isRequired: false },
      { name: "出厂编号", dataType: "字符型", measurementType: "—", isRequired: false },
      { name: "设备代码", dataType: "字符型", measurementType: "—", isRequired: false },
      { name: "设计使用寿命", dataType: "数值型", measurementType: "—", isRequired: false },
      { name: "设计单位", dataType: "字符型", measurementType: "—", isRequired: false },
      { name: "投运日期", dataType: "日期型", measurementType: "—", isRequired: false },
      { name: "储存介质", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "安装位置（场地）", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "注册登记证号（特种设备）", dataType: "字符型", measurementType: "—", isRequired: false },
      { name: "数量", dataType: "数值型", measurementType: "数量 1", isRequired: true },
      { name: "所属单位", dataType: "字符型", measurementType: "—", isRequired: false }
    ]
  },

  // 仪表专业
  "表 C.273": {
    tableName: "bas267_temperature_transmitter_params",
    displayName: "BAS267 温度变送器基础参数表",
    fields: [
      { name: "仪表型号", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "制造厂家", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "供货厂家", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "仪表名称", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "测量范围", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "准确度", dataType: "字符型", measurementType: "—", isRequired: true }
    ]
  },
  "表 C.338": {
    tableName: "bas332_actuator_params",
    displayName: "BAS332 执行机构基础参数表",
    fields: [
      { name: "规格", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "型号", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "制造厂家", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "供货厂家", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "仪表名称", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "精度", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "测量范围", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "允许误差", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "电源", dataType: "字符型", measurementType: "—", isRequired: true }
    ]
  },

  // 电气专业
  "表 C.355": {
    tableName: "bas349_power_generation_params",
    displayName: "BAS349 发电设备基础参数表",
    fields: [
      { name: "规格", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "型号", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "制造厂家", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "供货厂家", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "设备名称", dataType: "字符型", measurementType: "—", isRequired: true }
    ]
  },
  "表 C.386": {
    tableName: "bas380_distribution_box_params",
    displayName: "BAS380 配电箱基础参数表",
    fields: [
      { name: "规格", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "型号", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "制造厂家", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "供货厂家", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "设备名称", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "额定电压", dataType: "数值型", measurementType: "电压 2", isRequired: true },
      { name: "额定电流", dataType: "数值型", measurementType: "电流 1", isRequired: true },
      { name: "操作机构型号", dataType: "字符型", measurementType: "—", isRequired: false }
    ]
  },
  "表 C.394": {
    tableName: "bas388_ordinary_lighting_params",
    displayName: "BAS388 普通灯具基础参数表",
    fields: [
      { name: "规格", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "型号", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "制造厂家", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "供货厂家", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "名称", dataType: "字符型", measurementType: "—", isRequired: true }
    ]
  },
  "表 C.395": {
    tableName: "bas389_explosion_proof_lighting_params",
    displayName: "BAS389 防爆灯具基础参数表",
    fields: [
      { name: "规格", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "型号", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "制造厂家", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "供货厂家", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "名称", dataType: "字符型", measurementType: "—", isRequired: true }
    ]
  },
  "表 C.397": {
    tableName: "bas391_grounding_device_params",
    displayName: "BAS391 接地装置基础参数表",
    fields: [
      { name: "规格", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "型号", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "制造厂家", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "供货厂家", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "设备名称", dataType: "字符型", measurementType: "—", isRequired: true }
    ]
  },

  // 通信专业
  "表 C.404": {
    tableName: "bas398_system_equipment_params",
    displayName: "BAS398 系统设备基础参数表",
    fields: [
      { name: "规格", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "型号", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "制造厂家", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "供货厂家", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "设备名称", dataType: "字符型", measurementType: "—", isRequired: true }
    ]
  },

  // 暖通专业
  "表 C.144": {
    tableName: "bas138_air_conditioning_params",
    displayName: "BAS138 空调设备基础参数表",
    fields: [
      { name: "规格", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "型号", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "制造厂家", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "供货厂家", dataType: "字符型", measurementType: "—", isRequired: true },
      { name: "设备名称", dataType: "字符型", measurementType: "—", isRequired: true }
    ]
  }
};

export async function POST(request: NextRequest) {
  try {
    console.log('开始创建设备分类专用参数表...');
    
    const database = new SqliteDatabase();
    
    // 为每个分类表创建对应的数据库表
    for (const [tableId, tableInfo] of Object.entries(classificationTables)) {
      console.log(`创建表: ${tableInfo.displayName} (${tableInfo.tableName})`);
      
      // 构建字段定义 - 处理中文字段名
      const fieldDefinitions = tableInfo.fields.map(field => {
        const fieldType = field.dataType === '数值型' ? 'REAL' : 
                         field.dataType === '日期型' ? 'TEXT' : 'TEXT';
        // 将中文字段名转换为安全的数据库列名
        const columnName = field.name
          .replace(/[^\w\u4e00-\u9fa5]/g, '_')
          .replace(/^(\d)/, '_$1') // 如果以数字开头，前面加下划线
          .replace(/（/g, '_')
          .replace(/）/g, '_')
          .replace(/\(/g, '_')
          .replace(/\)/g, '_');
        
        return `\`${columnName}\` ${fieldType}`;
      }).join(',\n        ');
      
      // 创建表的SQL
      const createTableSQL = `
        CREATE TABLE IF NOT EXISTS ${tableInfo.tableName} (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          device_id INTEGER NOT NULL,
          ${fieldDefinitions},
          data_source TEXT DEFAULT '',
          created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
          updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
          FOREIGN KEY (device_id) REFERENCES devices (id) ON DELETE CASCADE
        )
      `;
      
      console.log(`执行SQL: ${createTableSQL}`);
      database.exec(createTableSQL);
      
      // 创建索引
      const indexSQL = `
        CREATE INDEX IF NOT EXISTS idx_${tableInfo.tableName}_device_id 
        ON ${tableInfo.tableName} (device_id)
      `;
      console.log(`创建索引: ${indexSQL}`);
      database.exec(indexSQL);
      
      console.log(`✓ 成功创建表 ${tableInfo.displayName}`);
    }
    
    console.log('所有设备分类专用参数表创建完成！');
    
    return NextResponse.json({ 
      success: true, 
      message: '设备分类专用参数表创建成功',
      tablesCreated: Object.keys(classificationTables).length,
      tableDetails: Object.entries(classificationTables).map(([tableId, tableInfo]) => ({
        tableId,
        tableName: tableInfo.tableName,
        displayName: tableInfo.displayName,
        fieldCount: tableInfo.fields.length
      }))
    });
    
  } catch (error) {
    console.error('创建设备分类专用参数表时出错:', error);
    return NextResponse.json(
      { error: '创建设备分类专用参数表失败', details: error instanceof Error ? error.message : String(error) },
      { status: 500 }
    );
  }
} 